package com.city.repository.report.format;

import com.city.domain.report.format.CtCopnoteFormatSql;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface CtCopnoteFormatSqlRepository extends JpaRepository<CtCopnoteFormatSql,Integer> {


    @Query(value = "SELECT c.id id ,c.address address,c.comment comment ,c.task_id task_id , c.update_time update_time ," +
            "o.name main_object_name , " +
            "c2.name main_content_name ," +
            "t.town_name town_name , " +
            "v.village_name village_name, " +
            "p.name report_name  " +
            "from ct_copnote as c  " +
            "left join city_copnote_object as o on c.main_object_id = o.id " +
            "LEFT  join city_copnote_maincontent as c2 on c.main_content_id = c2.id " +
            "LEFT  join city_town as t on c.town_id = t.town_id " +
            "LEFT  JOIN city_village as v on c.village_id = v.village_id " +
            "LEFT JOIN  ct_user as p on c.report_id = p.id  order by c.update_time desc limit ?1,?2",nativeQuery = true)
    List<CtCopnoteFormatSql> findByFromat(long page,long limit);

    @Query(value = "SELECT count(*) from ct_copnote",nativeQuery = true)
    long countAll();
}

